<?php

define("CREATE_ACCOUNT_TABLE", "CREATE TABLE `ACCOUNT` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `EMAIL` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `PASSWORD` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `TYPE` char(1) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `EMAIL` (`EMAIL`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8");

define("CREATE_PROFILE_TABLE", "CREATE TABLE `PROFILE` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `NAME` varchar(10) NOT NULL,
  `ADDRESS` varchar(100) NOT NULL,
  `PHONE` varchar(20) NOT NULL,
  `ACCOUNT_ID` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `PROFILE_ACCOUNT_FK` (`ACCOUNT_ID`),
  CONSTRAINT `PROFILE_ACCOUNT_FK` FOREIGN KEY (`ACCOUNT_ID`) REFERENCES `ACCOUNT` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8");

define("CREATE_PRODUCT_TYPE_TABLE", "CREATE TABLE `PRODUCT_TYPE` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8");

define("CREATE_PRODUCT_TABLE", "CREATE TABLE `PRODUCT` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) NOT NULL,
  `DESCRIPTION` text NOT NULL,
  `TYPE_ID` bigint(20) unsigned NOT NULL,
  `BUY_TIMES` int(10) unsigned NOT NULL,
  `PRICE` int(10) unsigned NOT NULL,
  `NUM` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `TYPE_FK` (`TYPE_ID`),
  CONSTRAINT `TYPE_FK` FOREIGN KEY (`TYPE_ID`) REFERENCES `PRODUCT_TYPE` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8");

define("CREATE_ORDERS_TABLE", "CREATE TABLE `ORDERS` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ACCOUNT_ID` bigint(20) unsigned NOT NULL,
  `PRODUCT_ID` bigint(20) unsigned NOT NULL,
  `NUM` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `BUY_ACCOUNT_FK` (`ACCOUNT_ID`),
  KEY `BUY_PRODUCT_FK` (`PRODUCT_ID`),
  CONSTRAINT `BUY_ACCOUNT_FK` FOREIGN KEY (`ACCOUNT_ID`) REFERENCES `ACCOUNT` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `BUY_PRODUCT_FK` FOREIGN KEY (`PRODUCT_ID`) REFERENCES `PRODUCT` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8");

define("CREATE_REPLY_TABLE", "CREATE TABLE `REPLY` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `CONTENT` text NOT NULL,
  `PRODUCT_ID` bigint(20) unsigned NOT NULL,
  `ACCOUNT_ID` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `REPLY_PRODUCT_FK` (`PRODUCT_ID`),
  KEY `REPLY_ACCOUNT_FK` (`ACCOUNT_ID`),
  CONSTRAINT `REPLY_ACCOUNT_FK` FOREIGN KEY (`ACCOUNT_ID`) REFERENCES `ACCOUNT` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `REPLY_PRODUCT_FK` FOREIGN KEY (`PRODUCT_ID`) REFERENCES `PRODUCT` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8");

define("CREATE_DB", "CREATE DATABASE LIDEJIA_ONLINE_SHOP");
define("USE_DB", "USE LIDEJIA_ONLINE_SHOP");
define("CREATE_USER", "GRANT ALL PRIVILEGES ON LIDEJIA_ONLINE_SHOP.* TO LDJ_ONLINE_SHOP@localhost IDENTIFIED BY 'LDJ_ONLINE_SHOP'");

class Application_Model_Install {

    public static function install($rootPassword, $adminPassword) {
        $db = new mysqli('localhost', 'root', $rootPassword);
        $db->set_charset("utf8");
        $db->query(CREATE_DB);
        $db->query(USE_DB);
        $db->query(CREATE_ACCOUNT_TABLE);
        $db->query(CREATE_PROFILE_TABLE);
        $db->query(CREATE_PRODUCT_TYPE_TABLE);
        $db->query(CREATE_PRODUCT_TABLE);
        $db->query(CREATE_REPLY_TABLE);
        $db->query(CREATE_ORDERS_TABLE);
        $db->query(CREATE_USER);
        $db->query("INSERT INTO ACCOUNT (ID, EMAIL, PASSWORD, TYPE) VALUES (1, 'admin','" . md5($adminPassword) . "', 'A')");
        Application_Model_Install::installTestData($db);
    }

    private static function installTestData($db) {
        $db->query("INSERT INTO ACCOUNT (ID, EMAIL, PASSWORD, TYPE) VALUES (2, 'lidejiasw@gmail.com','" . md5("lidejia") . "', 'U')");
        $db->query("INSERT INTO PROFILE (ID, NAME, ADDRESS, PHONE, ACCOUNT_ID) VALUES (2, '李德佳', '南京大学', '15151810907', 2)");
        $db->query("INSERT INTO PRODUCT_TYPE (ID, NAME) VALUES (1, '书籍')");
        $db->query("INSERT INTO PRODUCT_TYPE (ID, NAME) VALUES (2, '服装')");
        $db->query("INSERT INTO PRODUCT_TYPE (ID, NAME) VALUES (3, '电子产品')");
        $db->query("INSERT INTO PRODUCT_TYPE (ID, NAME) VALUES (4, '武器')");
        $db->query("INSERT INTO PRODUCT (ID, NAME, DESCRIPTION, TYPE_ID, BUY_TIMES, PRICE, NUM) VALUES (1, 'Thinking In Java', 'Java圣经', 1, 10, 70, 190)");
        $db->query("INSERT INTO PRODUCT (ID, NAME, DESCRIPTION, TYPE_ID, BUY_TIMES, PRICE, NUM) VALUES (2, 'AK47', '恐怖分子必备产品', 4, 5, 10000, 45)");
        $db->query("INSERT INTO PRODUCT (ID, NAME, DESCRIPTION, TYPE_ID, BUY_TIMES, PRICE, NUM) VALUES (3, '核弹头', '数量有限，预购从速', 4, 2, 10000000, 8)");
        $db->query("INSERT INTO PRODUCT (ID, NAME, DESCRIPTION, TYPE_ID, BUY_TIMES, PRICE, NUM) VALUES (4, '隐形战斗机', '第五代技术', 4, 5, 5000000, 15)");
        $db->query("INSERT INTO PRODUCT (ID, NAME, DESCRIPTION, TYPE_ID, BUY_TIMES, PRICE, NUM) VALUES (5, 'Legend', '全铝机身', 3, 50, 2000, 450)");
        $db->query("INSERT INTO PRODUCT (ID, NAME, DESCRIPTION, TYPE_ID, BUY_TIMES, PRICE, NUM) VALUES (6, 'iPAD 2', '第二代', 3, 20, 4000, 80)");
        $db->query("INSERT INTO PRODUCT (ID, NAME, DESCRIPTION, TYPE_ID, BUY_TIMES, PRICE, NUM) VALUES (7, 'Unix环境高级编程', 'Unix程序设计圣经', 1, 30, 100, 170)");
        $db->query("INSERT INTO PRODUCT (ID, NAME, DESCRIPTION, TYPE_ID, BUY_TIMES, PRICE, NUM) VALUES (8, '犀利哥套装', '彰显您的犀利风采', 2, 1000, 400, 200)");
    }

}

